# 导入包
from matplotlib import pyplot as plt
import pandas as pd
import statsmodels.formula.api as smf
from sqlalchemy import create_engine
import pymysql
import statsmodels.api as sm
import numpy as np
import seaborn as sns

db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'root',
    'database': 'tushare',
    'port': 3306,
    'charset': 'utf8mb4'   # 添加字符集设置
}
engine = create_engine(
    f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
)
conn = pymysql.connect(**db_config)
chunk_size = 10000

df = pd.read_sql_query("""
    SELECT d.*, m. buy_lg_vol,m.sell_lg_vol, m.buy_elg_vol,m.sell_elg_vol,m.net_mf_ol,i.vol as i_vol,i.closes a 
    FROM date_1 d
    join moneyflows m on m,ts_code = d.ts_code and d.trade_date = m.trade_date
    left join index_daily i on i.trade_date = d.trade_date and i.ts_code = '399001.SZ'
    MHERE d.trade_date BETWEEN '2023-01-01' and '2023-12-31' and d.ts_code ='e80001.SZ'
    """,
    conn ,
    chunksize=chunk_size)
df1 =pd.concat(df, ignore_index=True)

df1['zd_closes'] = round((df1['closes']- df1['closes'].shift(1))/df1['closes'])
df1 ['zs_closes'] = round((df1['i_yloses'] - df1['i_vloses' ].shift(1)) / df1['i_vloses' ].shift(1),2)
df1[ 'zs_vlo'] =round((df1[ 'i_vo1 '] -df1['i_ypl '].shift(1))/df1['i_vol '].shift(1),2)
print(df1.head)
#处理缺失值数据
df1 = df1.dropna(subset=[ 'zd_closes', 'zs_closes', 'zs_closes'])
print(df1.head)

ex= ['zd_closes', 'id', 'ts_code', 'trade_date','the_date', 'opens', 'high ','low', 'closes','pre_closes','changes ', 'pct_chg', 'amount ', 'vol', 'buy_elg_ vol', 'i_vol ', 'i_vloses', 'zs_vlo']
number = df1.select_dtypes(include=[ 'number']).columns.tolist()

newList =[col for col in number if col not in ex]

formuls = 'zd_closes ~ '+'+'.join(newList)
res = smf.ols(formuls, data=df1).fit()
print(res.summary())

pit.figure(figsize=(10,6))
sns.heatmap(correlation_matrix,annot=True, cmap='coolwarm', cbar=True, fmt='.2f',linewidths=.5)
plt.show()